﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.Collections;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;

namespace TS_XY_Train_Repaire.Materials
{
    public partial class Materials_Manage_ManaForm : DevExpress.XtraEditors.XtraForm
    {

        public Materials_Manage_ManaForm()
        {
            InitializeComponent();
        }


        public string _mid = "";
        public int _mdeep = -1;
        public string _mlevel = ",";
        public string _mstr = "";



        private void Materials_Manage_ManaForm_Load(object sender, EventArgs e)
        {
            bbi_Add.Enabled = true;
            bbi_Edit.Enabled = false;
            bbi_Del.Enabled = false;
            loadTree_Materials();
            txttlspNumber.Text = "1";
            pb1.Visible = false;
        }

        private void loadTree_Materials() //物资材料目录
        {
            treeList_Materials.Nodes.Clear();
            treeList_Materials.Refresh();
            LeverString_SysTreeMainList _ls = new LeverString_SysTreeMainList();
            ArrayList pList = new ArrayList();
            //菜单头
            _ls = new LeverString_SysTreeMainList();
            _ls.f_id = "M-1";
            _ls.f_name = "物资材料目录";
            _ls.f_imgindex = 4;
            _ls.f_code = "编号";
            pList.Add(_ls);

            //一级菜单目录
            try
            {
                string sqlStr = "select * from TS_S_T_B_Materials where f_del=0 and f_type=0 and f_deep=0 and f_changetype <> 2 order by f_code asc";
                SqlConnection conn = new SqlConnection(TS_Win_Class.GetSQlConnStr());
                conn.Open();
                SqlCommand comm = new SqlCommand(sqlStr, conn);
                SqlDataReader read = comm.ExecuteReader();
                string _strUPID = "and (";
                int i = 0;
                while (read.Read())
                {
                    _ls = new LeverString_SysTreeMainList();
                    _ls.f_id = read["f_id"].ToString();
                    _ls.f_name = read["f_name"].ToString();
                    _ls.f_parent = "M-1";
                    _ls.f_code = read["f_code"].ToString();
                    if (read["f_type"].ToString() == "0")
                    {
                        _ls.f_imgindex = 5;
                    }
                    else
                    {
                        _ls.f_imgindex = 9;
                    }
                    if (i == 0)
                    {
                        _strUPID += "(f_level like '%," + read["f_id"].ToString() + ",%')";
                    }
                    else
                    {
                        _strUPID += "or (f_level like '%," + read["f_id"].ToString() + ",%')";
                    }
                    pList.Add(_ls);
                    i++;
                }
                _strUPID += " )";
                //if (_strUPID.Length > 7)    //显示2级及以下分类
                //{
                //    read.Close();
                //    sqlStr = "select * from TS_S_T_B_Materials where f_deep>0 and f_del=0 and f_type=0 " + _strUPID + " order by f_deep,f_level,f_code asc";
                //    comm = new SqlCommand(sqlStr, conn);
                //    read = comm.ExecuteReader();
                //    string _UPID = "";
                //    while (read.Read())
                //    {
                //        _ls = new LeverString_SysTreeMainList();

                //        _ls.f_id = read["f_id"].ToString();
                //        _ls.f_name = read["f_name"].ToString();
                //        _ls.f_deep = read["f_deep"].ToString();
                //        _ls.f_level = read["f_level"].ToString();
                //        _ls.f_type = read["f_type"].ToString();
                //        _ls.f_code = read["f_code"].ToString();
                //        _UPID = read["f_level"].ToString();
                //        _UPID = _UPID.Substring(0, _UPID.Length - 1);
                //        _UPID = _UPID.Substring(_UPID.LastIndexOf(',') + 1);
                //        _ls.f_parent = _UPID;
                //        if (read["f_type"].ToString() == "0")
                //        {
                //            _ls.f_imgindex = 5;
                //        }
                //        else
                //        {
                //            _ls.f_imgindex = 9;
                //        }
                //        pList.Add(_ls);
                //    }
                //}
                read.Close();
                conn.Close();
                conn.Dispose();
            }
            catch { }
            treeList_Materials.DataSource = pList;
            treeList_Materials.ExpandAll();
        }

        private void girdList()
        {
            DataTable dt = new DataTable();
            TS_Win_Class.FilledDataTable("select f_id,f_name, f_code, f_code_map, f_code_factory, f_code_station, f_code_type, f_cz, f_unit, f_level, f_deep, f_num, f_upnum, f_lownum,  f_indate,f_worktype, case f_worktype when 1 then '线上'  when 0 then '线下' end  as f_worktype1, f_changetype, case f_changetype when 0 then '偶换件' when 1 then '必换件' end  as f_changetype1 from TS_S_T_B_Materials where f_del=0 and f_changetype<>2 and f_type=1 ", dt);
            Gird_Materials.DataSource = dt;


        }

        private void girdList(string strA)
        {
            DataTable dt = new DataTable();
            TS_Win_Class.FilledDataTable("select f_id,f_name, f_code, f_code_map, f_code_factory, f_code_station, f_code_type, f_cz, f_unit, f_level, f_deep, f_num, f_upnum, f_lownum,  f_indate,f_worktype, case f_worktype when 1 then '线上'  when 0 then '线下' end  as f_worktype1,f_changetype, case f_changetype when 0 then '偶换件' when 1 then '必换件' end  as f_changetype1 from TS_S_T_B_Materials where f_del=0 and f_changetype<>2 and f_type=1 " + strA + "", dt);
            Gird_Materials.DataSource = dt;
        }

        private void treeList_Materials_FocusedNodeChanged(object sender, DevExpress.XtraTreeList.FocusedNodeChangedEventArgs e)
        {
            try
            {
                if (treeList_Materials.FocusedNode["f_id"].ToString().Substring(0, 1) == "M")
                {
                    bbi_Add.Enabled = true;
                    bbi_Edit.Enabled = false;
                    bbi_Del.Enabled = false;
                }
                else
                {
                    bbi_Add.Enabled = true;
                    bbi_Edit.Enabled = true;
                    bbi_Del.Enabled = true;
                    //if (treeList_Materials.FocusedNode["f_id"].ToString() != "" && treeList_Materials.FocusedNode["f_id"].ToString() != "M-1")
                    //{
                    //    GetAllcount(treeList_Materials.FocusedNode["f_id"].ToString());
                    //    GetSqlPage(strat, int.Parse(treeList_Materials.FocusedNode["f_id"].ToString()));
                    //}
                    //else
                    //{
                    //}
                }
            }
            catch
            {
            }
        }

        private void Gird_Materials_Click(object sender, EventArgs e)
        {
            bbi_Add.Enabled = false;
            bbi_Del.Enabled = true;
            bbi_Edit.Enabled = true;
        }

        private void bbi_Reload_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            loadTree_Materials();
            Gird_Materials.DataSource = null;
        }

        private void bbi_Add_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            LeverString_SysTreeMainList _ls = new LeverString_SysTreeMainList();
            _ls.f_id = treeList_Materials.FocusedNode["f_id"].ToString();
            _ls.f_name = treeList_Materials.FocusedNode["f_name"].ToString();
            _ls.f_deep = treeList_Materials.FocusedNode["f_deep"].ToString();
            _ls.f_level = treeList_Materials.FocusedNode["f_level"].ToString();
            _ls.f_type = treeList_Materials.FocusedNode["f_type"].ToString();
            _ls.f_code = treeList_Materials.FocusedNode["f_code"].ToString();
            Materials_Manage_Details _uc = new Materials_Manage_Details();
            _uc._tl = _ls;
            _uc._LoadType = "Add";
            if (_uc.ShowDialog() == DialogResult.OK)
            {
                loadTree_Materials();
                treeList_Materials.SetFocusedNode(treeList_Materials.FindNodeByFieldValue("f_id", _ls.f_id));
                girdList();
            }
            else
            {
                loadTree_Materials();
                treeList_Materials.SetFocusedNode(treeList_Materials.FindNodeByFieldValue("f_id", _ls.f_id));
                girdList();
            }
        }

        private void treeList_Materials_DoubleClick(object sender, EventArgs e)
        {
            try
            {
                if (treeList_Materials.FocusedNode["f_id"].ToString().Substring(0, 1) == "M")
                {
                    girdList();
                }
                else
                {
                    girdList("  and f_level like '" + treeList_Materials.FocusedNode["f_level"].ToString() + "" + treeList_Materials.FocusedNode["f_id"].ToString() + ",%'");
                }
            }
            catch
            {
            }
        }

        private void bbi_Edit_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            LeverString_SysTreeMainList _ls = new LeverString_SysTreeMainList();
            _ls.f_id = treeList_Materials.FocusedNode["f_id"].ToString();
            _ls.f_name = treeList_Materials.FocusedNode["f_name"].ToString();
            _ls.f_deep = treeList_Materials.FocusedNode["f_deep"].ToString();
            _ls.f_level = treeList_Materials.FocusedNode["f_level"].ToString();
            _ls.f_type = treeList_Materials.FocusedNode["f_type"].ToString();
            _ls.f_code = treeList_Materials.FocusedNode["f_code"].ToString();
            Materials_Manage_Details _uc = new Materials_Manage_Details();
            _uc._tl = _ls;
            _uc._LoadType = "Edit";
            _uc._id = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_id").ToString();
            _uc._name = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_name").ToString();
            _uc._code = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_code").ToString();
            _uc._code_factory = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_code_factory").ToString();
            _uc._code_map = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_code_map").ToString();
            _uc._code_station = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_code_station").ToString();
            _uc._code_type = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_code_type").ToString();
            _uc._cz = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_cz").ToString();
            _uc._indate = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_indate").ToString();
            _uc._lownum = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_lownum").ToString();
            _uc._upnum = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_upnum").ToString();
            _uc._num = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_num").ToString();
            _uc._worktype = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_worktype").ToString();
            _uc._unit = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_unit").ToString();
            _uc._changetype = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_changetype").ToString();
            if (Gird_Materials.Focused == true)
            {
                _uc._Select = "1";
            }
            if (_uc.ShowDialog() == DialogResult.OK)
            {
                loadTree_Materials();
                treeList_Materials.SetFocusedNode(treeList_Materials.FindNodeByFieldValue("f_id", _ls.f_id));
                girdList();
            }
            else
            {
            }
        }

        private void bbi_Del_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            LeverString_SysTreeMainList _ls = new LeverString_SysTreeMainList();
            _ls.f_id = treeList_Materials.FocusedNode["f_id"].ToString();
            _ls.f_name = treeList_Materials.FocusedNode["f_name"].ToString();
            _ls.f_deep = treeList_Materials.FocusedNode["f_deep"].ToString();
            _ls.f_level = treeList_Materials.FocusedNode["f_level"].ToString();
            _ls.f_type = treeList_Materials.FocusedNode["f_type"].ToString();
            _ls.f_code = treeList_Materials.FocusedNode["f_code"].ToString();
            Materials_Manage_Details _uc = new Materials_Manage_Details();
            _uc._tl = _ls;
            _uc._LoadType = "Del";
            _uc._id = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_id").ToString();
            _uc._name = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_name").ToString();
            _uc._code = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_code").ToString();
            _uc._code_factory = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_code_factory").ToString();
            _uc._code_map = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_code_map").ToString();
            _uc._code_station = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_code_station").ToString();
            _uc._code_type = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_code_type").ToString();
            _uc._cz = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_cz").ToString();
            _uc._indate = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_indate").ToString();
            _uc._lownum = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_lownum").ToString();
            _uc._upnum = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_upnum").ToString();
            _uc._num = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_num").ToString();
            _uc._worktype = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_worktype").ToString();
            _uc._unit = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_unit").ToString();
            _uc._changetype = CamGridView.GetRowCellDisplayText(CamGridView.FocusedRowHandle, "f_changetype").ToString();
            if (Gird_Materials.Focused == true)
            {

                _uc._Select = "1";
            }
            if (_uc.ShowDialog() == DialogResult.OK)
            {
                loadTree_Materials();
                treeList_Materials.SetFocusedNode(treeList_Materials.FindNodeByFieldValue("f_id", _ls.f_id));
                girdList();
            }
            else
            {
            }
        }

        private void bt_out_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (Directory.Exists(TS_Win_Class._EXP_Direct)==false)
	     {
             Directory.CreateDirectory(TS_Win_Class._EXP_Direct);
	     }  
            if (MessageBox.Show("文件导出为" + TS_Win_Class._EXP_Direct + System.DateTime.Today.Year.ToString() + "-" + System.DateTime.Today.Month.ToString() + "-" + System.DateTime.Today.Day.ToString() + "物资导出列表.xls", "注意", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK)
            {
                Gird_Materials.ExportToXls(TS_Win_Class._EXP_Direct + System.DateTime.Today.Year.ToString() + "-" + System.DateTime.Today.Month.ToString() + "-" + System.DateTime.Today.Day.ToString() + "物资列表.xls");
            }
        }

        #region 连接Excel  读取Excel数据   并返回DataSet数据集合
        /// <summary>
        /// 连接Excel  读取Excel数据   并返回DataSet数据集合
        /// </summary>
        /// <param name="filepath">Excel服务器路径</param>
        /// <param name="tableName">Excel表名称</param>
        /// <returns></returns>
        public static System.Data.DataSet ExcelSqlConnection(string filepath, string tableName)
        {
            /// string strCon = "Provider=Microsoft.Ace.OleDb.12.0;Data Source = " + filepath + ";Extended Properties = \"Excel 12.0;HDR=YES;IMEX=\"";
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";" + "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            OleDbConnection ExcelConn = new OleDbConnection(strCon);
            try
            {
                string strCom = string.Format("SELECT * FROM [Sheet$]");
                ExcelConn.Open();
                OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn);
                DataSet ds = new DataSet();
                myCommand.Fill(ds, "[" + tableName + "$]");
                ExcelConn.Close();
                return ds;
            }
            catch
            {
                ExcelConn.Close();
                return null;
            }
        }
        #endregion


        private void bt_in_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string sql = "";
                if (openFileDialog1.FileName == "")//HasFile用来检查FileUpload是否有指定文件
                {
                    MessageBox.Show("请您选择Excel文件");
                    return;//当无文件时,返回
                }
                string IsXls = System.IO.Path.GetExtension(openFileDialog1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
                if (IsXls != ".xls")
                {
                    MessageBox.Show("只可以选择97-03版本Excel文件");
                    return;//当选择的不是Excel文件时,返回
                }
                string filename = openFileDialog1.SafeFileName;              //获取Execle文件名  DateTime日期函数
                //string savePath = Server.MapPath(("upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
                string savePath = openFileDialog1.FileName;
                //FileUpload1.SaveAs(savePath);                        //SaveAs 将上传的文件内容保存在服务器上

                DataSet ds = ExcelSqlConnection(savePath, filename);           //调用自定义方法
                DataRow[] dr = ds.Tables[0].Select();            //定义一个DataRow数组
                int rowsnum = ds.Tables[0].Rows.Count;
                if (rowsnum == 0)
                {
                    MessageBox.Show("Excel表为空表,无数据!");   //当Excel表为空时,对用户进行提示
                }
                else
                {
                    pb1.Minimum = 0;
                    pb1.Value = 0;
                    pb1.Maximum = rowsnum;
                    pb1.Visible = true; ;            

                    for (int i = 0; i < dr.Length; i++)
                    {
                        //timer1.Enabled = true;
                       pb1.Value = i;
                        string _f_name = dr[i]["物资材料名称"].ToString();
                        string _f_code = dr[i]["物资编号"].ToString();
                        string _f_code_map = dr[i]["图编号"].ToString();
                        string _f_code_factory = dr[i]["出厂编号"].ToString();
                        string _f_code_station = dr[i]["段内编号"].ToString();
                        string _f_code_type = dr[i]["规格型号"].ToString();
                        string _f_cz = dr[i]["物资材质"].ToString();
                        string _f_unit = dr[i]["物资计量单位"].ToString();
                        string _f_num = dr[i]["定装数量"].ToString();
                        string _f_upnum = dr[i]["库存上限"].ToString();
                        string _f_lownum = dr[i]["库存下限"].ToString();
                        string _f_indate = dr[i]["使用寿命"].ToString();
                        string _f_worktype = dr[i]["类型1（0、线下1、线上）"].ToString();
                        string _f_changetype = dr[i]["类型2（0、偶换件1、必换件）"].ToString();
                        //Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
                        //dr[i]["物资编号"].ToString().Substring(0,1)

                        if (_f_code.Length >= 12)
                        {
                            _mstr = "Select * from TS_S_T_B_Materials where f_code='" + dr[i]["物资编号"].ToString().Substring(0, 1) + "' and f_type=0 and f_del=0";
                            _mid = TS_Win_Class.GetSQlFieldValue_1(_mstr, "f_id");
                            string _mms = "Select count(*) as f_code from TS_S_T_B_Materials where f_type=1 and f_del=0  and  f_changetype<>2   and f_code='" + _f_code + "'";
                            string _mcode = TS_Win_Class.GetSQlFieldValue_1(_mms,"f_code");
                            if (_mcode == "0")
                            {

                                if (_mid == "")
                                {
                                    _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(0, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",',',0,0)");
                                    _mlevel = _mlevel + _mid + ",";
                                    _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(1, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'" + _mlevel + "',1,0)");
                                    _mlevel = _mlevel + _mid + ",";
                                    _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(2, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'" + _mlevel + "',2,0)");
                                    _mlevel = _mlevel + _mid + ",";
                                    _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(3, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'" + _mlevel + "',3,0)");
                                    _mlevel = _mlevel + _mid + ",";
                                    _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(4, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'" + _mlevel + "',4,0)");
                                    _mlevel = _mlevel + _mid + ",";
                                    _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(5, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'" + _mlevel + "',5,0)");
                                    _mlevel = _mlevel + _mid + ",";
                                    sql = "insert into TS_S_T_B_Materials(f_name, f_code, f_code_map, f_code_factory, f_code_station, f_code_type, f_cz, f_unit, f_num, f_upnum, f_lownum, f_indate,  f_worktype, f_changetype,f_type,f_workerid,f_deep,f_level) values('" + _f_name + "','" + _f_code + "','" + _f_code_map + "','" + _f_code_factory + "','" + _f_code_station + "','" + _f_code_type + "','" + _f_cz + "','" + _f_unit + "','" + _f_num + "','" + _f_upnum + "','" + _f_lownum + "','" + _f_indate + "','" + _f_worktype + "','" + _f_changetype + "','1'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'6','" + _mlevel + "')";
                                    TS_Win_Class.MySqlCom(sql);
                                    _mlevel = ",";
                                }
                                else
                                {
                                    _mlevel = TS_Win_Class.GetSQlFieldValue_1(_mstr, "f_level") + _mid + ",";
                                    _mstr = "Select * from TS_S_T_B_Materials where f_code='" + dr[i]["物资编号"].ToString().Substring(1, 1) + "' and f_type=0 and f_del=0  and f_level = '" + _mlevel + "'";
                                    _mid = TS_Win_Class.GetSQlFieldValue_1(_mstr, "f_id");

                                    if (_mid == "")
                                    {
                                        _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(1, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'" + _mlevel + "',1,0)");
                                        _mlevel = _mlevel + _mid + ",";
                                        _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(2, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'" + _mlevel + "',2,0)");
                                        _mlevel = _mlevel + _mid + ",";
                                        _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(3, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'" + _mlevel + "',3,0)");
                                        _mlevel = _mlevel + _mid + ",";
                                        _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(4, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'" + _mlevel + "',4,0)");
                                        _mlevel = _mlevel + _mid + ",";
                                        _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(5, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'" + _mlevel + "',5,0)");
                                        _mlevel = _mlevel + _mid + ",";
                                        sql = "insert into TS_S_T_B_Materials(f_name, f_code, f_code_map, f_code_factory, f_code_station, f_code_type, f_cz, f_unit, f_num, f_upnum, f_lownum, f_indate,  f_worktype, f_changetype,f_type,f_workerid,f_deep,f_level) values('" + _f_name + "','" + _f_code + "','" + _f_code_map + "','" + _f_code_factory + "','" + _f_code_station + "','" + _f_code_type + "','" + _f_cz + "','" + _f_unit + "','" + _f_num + "','" + _f_upnum + "','" + _f_lownum + "','" + _f_indate + "','" + _f_worktype + "','" + _f_changetype + "','1'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'6','" + _mlevel + "')";
                                        TS_Win_Class.MySqlCom(sql);
                                        _mlevel = ",";
                                    }
                                    else
                                    {
                                        _mlevel = TS_Win_Class.GetSQlFieldValue_1(_mstr, "f_level") + _mid + ",";
                                        _mstr = "Select * from TS_S_T_B_Materials where f_code='" + dr[i]["物资编号"].ToString().Substring(2, 1) + "' and f_type=0 and f_del=0  and f_level = '" + _mlevel + "'";
                                        _mid = TS_Win_Class.GetSQlFieldValue_1(_mstr, "f_id");

                                        if (_mid == "")
                                        {
                                            _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(2, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'" + _mlevel + "',2,0)");
                                            _mlevel = _mlevel + _mid + ",";
                                            _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(3, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'" + _mlevel + "',3,0)");
                                            _mlevel = _mlevel + _mid + ",";
                                            _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(4, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'" + _mlevel + "',4,0)");
                                            _mlevel = _mlevel + _mid + ",";
                                            _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(5, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'" + _mlevel + "',5,0)");
                                            _mlevel = _mlevel + _mid + ",";
                                            sql = "insert into TS_S_T_B_Materials(f_name, f_code, f_code_map, f_code_factory, f_code_station, f_code_type, f_cz, f_unit, f_num, f_upnum, f_lownum, f_indate,  f_worktype, f_changetype,f_type,f_workerid,f_deep,f_level) values('" + _f_name + "','" + _f_code + "','" + _f_code_map + "','" + _f_code_factory + "','" + _f_code_station + "','" + _f_code_type + "','" + _f_cz + "','" + _f_unit + "','" + _f_num + "','" + _f_upnum + "','" + _f_lownum + "','" + _f_indate + "','" + _f_worktype + "','" + _f_changetype + "','1'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'6','" + _mlevel + "')";
                                            TS_Win_Class.MySqlCom(sql);
                                            _mlevel = ",";
                                        }
                                        else
                                        {
                                            _mlevel = TS_Win_Class.GetSQlFieldValue_1(_mstr, "f_level") + _mid + ",";
                                        _mstr = "Select * from TS_S_T_B_Materials where f_code='" + dr[i]["物资编号"].ToString().Substring(3, 1) + "' and f_type=0 and f_del=0  and f_level = '" + _mlevel + "'";
                                        _mid = TS_Win_Class.GetSQlFieldValue_1(_mstr, "f_id");

                                        if (_mid == "")
                                        {
                                            _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(3, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'" + _mlevel + "',3,0)");
                                            _mlevel = _mlevel + _mid + ",";
                                            _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(4, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'" + _mlevel + "',4,0)");
                                            _mlevel = _mlevel + _mid + ",";
                                            _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(5, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'" + _mlevel + "',5,0)");
                                            _mlevel = _mlevel + _mid + ",";
                                            sql = "insert into TS_S_T_B_Materials(f_name, f_code, f_code_map, f_code_factory, f_code_station, f_code_type, f_cz, f_unit, f_num, f_upnum, f_lownum, f_indate,  f_worktype, f_changetype,f_type,f_workerid,f_deep,f_level) values('" + _f_name + "','" + _f_code + "','" + _f_code_map + "','" + _f_code_factory + "','" + _f_code_station + "','" + _f_code_type + "','" + _f_cz + "','" + _f_unit + "','" + _f_num + "','" + _f_upnum + "','" + _f_lownum + "','" + _f_indate + "','" + _f_worktype + "','" + _f_changetype + "','1'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'6','" + _mlevel + "')";
                                            TS_Win_Class.MySqlCom(sql);
                                            _mlevel = ",";
                                        }
                                        else
                                        {
                                            _mlevel = TS_Win_Class.GetSQlFieldValue_1(_mstr, "f_level") + _mid + ",";
                                            _mstr = "Select * from TS_S_T_B_Materials where f_code='" + dr[i]["物资编号"].ToString().Substring(4, 1) + "' and f_type=0 and f_del=0  and f_level = '" + _mlevel + "'";
                                            _mid = TS_Win_Class.GetSQlFieldValue_1(_mstr, "f_id");

                                            if (_mid == "")
                                            {
                                               _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(4, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'" + _mlevel + "',4,0)");
                                                _mlevel = _mlevel + _mid + ",";
                                                _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(5, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'" + _mlevel + "',5,0)");
                                                _mlevel = _mlevel + _mid + ",";
                                                sql = "insert into TS_S_T_B_Materials(f_name, f_code, f_code_map, f_code_factory, f_code_station, f_code_type, f_cz, f_unit, f_num, f_upnum, f_lownum, f_indate,  f_worktype, f_changetype,f_type,f_workerid,f_deep,f_level) values('" + _f_name + "','" + _f_code + "','" + _f_code_map + "','" + _f_code_factory + "','" + _f_code_station + "','" + _f_code_type + "','" + _f_cz + "','" + _f_unit + "','" + _f_num + "','" + _f_upnum + "','" + _f_lownum + "','" + _f_indate + "','" + _f_worktype + "','" + _f_changetype + "','1'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'6','" + _mlevel + "')";
                                                TS_Win_Class.MySqlCom(sql);
                                                _mlevel = ",";
                                            }

                                            else
                                            {
                                                _mlevel = TS_Win_Class.GetSQlFieldValue_1(_mstr, "f_level") + _mid + ",";
                                                _mstr = "Select * from TS_S_T_B_Materials where f_code='" + dr[i]["物资编号"].ToString().Substring(5, 1) + "' and f_type=0 and f_del=0 and f_level = '" + _mlevel + "'";
                                                _mid = TS_Win_Class.GetSQlFieldValue_1(_mstr, "f_id");

                                                if (_mid == "")
                                                {
                                                    _mid = TS_Win_Class.MySqlComGetID("insert into TS_S_T_B_Materials(f_code,f_workerid,f_level,f_deep,f_type)  values('" + dr[i]["物资编号"].ToString().Substring(5, 1) + "'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'" + _mlevel + "',5,0)");
                                                    _mlevel = _mlevel + _mid + ",";
                                                    sql = "insert into TS_S_T_B_Materials(f_name, f_code, f_code_map, f_code_factory, f_code_station, f_code_type, f_cz, f_unit, f_num, f_upnum, f_lownum, f_indate,  f_worktype, f_changetype,f_type,f_workerid,f_deep,f_level) values('" + _f_name + "','" + _f_code + "','" + _f_code_map + "','" + _f_code_factory + "','" + _f_code_station + "','" + _f_code_type + "','" + _f_cz + "','" + _f_unit + "','" + _f_num + "','" + _f_upnum + "','" + _f_lownum + "','" + _f_indate + "','" + _f_worktype + "','" + _f_changetype + "','1'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'6','" + _mlevel + "')";
                                                    TS_Win_Class.MySqlCom(sql);
                                                    _mlevel = ",";
                                                }
                                                else
                                                {
                                                    _mlevel = TS_Win_Class.GetSQlFieldValue_1(_mstr, "f_level") + _mid + ",";
                                                    sql = "insert into TS_S_T_B_Materials(f_name, f_code, f_code_map, f_code_factory, f_code_station, f_code_type, f_cz, f_unit, f_num, f_upnum, f_lownum, f_indate,  f_worktype, f_changetype,f_type,f_workerid,f_deep,f_level) values('" + _f_name + "','" + _f_code + "','" + _f_code_map + "','" + _f_code_factory + "','" + _f_code_station + "','" + _f_code_type + "','" + _f_cz + "','" + _f_unit + "','" + _f_num + "','" + _f_upnum + "','" + _f_lownum + "','" + _f_indate + "','" + _f_worktype + "','" + _f_changetype + "','1'," + TS_Win_Locomotive_MainForm.pUserInfoMain.GuId + ",'6','" + _mlevel + "')";
                                                    TS_Win_Class.MySqlCom(sql);
                                                    _mlevel = ",";
                                                }
                                            }
                                       }
                                      }
                                    }
                                 }
                            }
                            else
                            {
                                //sql = "select top 1 f_code from TS_S_T_B_Materials";
                            }


                        }
                        else
                        {
                            MessageBox.Show("有部分物资编号不足12位或者重复的未导入!");
                        }
                        pb1.Value++;
                    }

                    
                    MessageBox.Show("Excle表导入成功!");
                    pb1.Visible = false;
                    loadTree_Materials();
                }
            }
        }

        private void Materials_Manage_ManaForm_FormClosing(object sender, FormClosingEventArgs e)
        {
            TS_Win_Locomotive_MainForm.htActionItem.Remove(this.Name);
        }



        //    #region  //分页数据展示//

        //    public static int strat = 1;
        //    //下一页
        //    private void TsMoveLastitem_Click(object sender, EventArgs e)
        //    {
        //        if (treeList_Materials.FocusedNode["f_id"].ToString() != "" && this.Number.Text != "0")
        //        {
        //            if (Convert.ToString(strat) == this.Number.Text.Trim())
        //            {
        //                // strat = int.Parse(this.Number.Text);
        //            }
        //            else
        //            {
        //                //strat++;
        //                //GetSqlPage(strat, int.Parse(treeList_Materials.FocusedNode["f_id"].ToString()));
        //                //txttlspNumber.Text = Convert.ToString(strat);
        //            }
        //        }
        //        else { }
        //        //this.Number.Text = Convert.ToString(strat);
        //    }

        //   //上一页
        //    private void TsMoveFirstitme_Click(object sender, EventArgs e)
        //    {
        //        if (treeList_Materials.FocusedNode["f_id"].ToString() != "")
        //        {
        //            if (strat == 1)
        //            {
        //                strat = 1;
        //                //this.Number.Text = Convert.ToString(strat);
        //                txttlspNumber.Text = "1";
        //            }
        //            else
        //            {
        //                strat--;
        //                GetSqlPage(strat, int.Parse(treeList_Materials.FocusedNode["f_id"].ToString()));
        //                //this.Number.Text = Convert.ToString(strat);
        //                txttlspNumber.Text =Convert.ToString(strat);
        //            }
        //        }
        //        else { }
        //    }

        //    //首页
        //    private void TsFirstItme_Click(object sender, EventArgs e)
        //    {
        //        if (treeList_Materials.FocusedNode["f_id"].ToString() != "")
        //        {
        //            //
        //            GetSqlPage(1, int.Parse(treeList_Materials.FocusedNode["f_id"].ToString()));
        //            //this.Number.Text = Convert.ToString(strat);
        //            this.txttlspNumber.Text ="1";
        //            strat =1;
        //        }
        //        else { }
        //    }

        //    //最后一页
        //    private void TsLastItem_Click(object sender, EventArgs e)
        //    {
        //        if (treeList_Materials.FocusedNode["f_id"].ToString() != "")
        //        {
        //            // MessageBox.Show("积极");
        //            strat = int.Parse(this.Number.Text.Trim());
        //            GetSqlPage(strat, int.Parse(treeList_Materials.FocusedNode["f_id"].ToString()));
        //            //this.Number.Text = Convert.ToString(strat);
        //            this.txttlspNumber.Text = this.Number.Text.Trim();
        //        }
        //        else {

        //        }
        //    }

        //     //转到
        //    private void tlspGoto_Click(object sender, EventArgs e)
        //    {
        //        if (treeList_Materials.FocusedNode["f_id"].ToString() != "" && treeList_Materials.FocusedNode["f_id"].ToString() != "M-1")
        //        {
        //            int txtnum=int.Parse(this.txttlspNumber.Text.Trim());
        //            int number=int.Parse(this.Number.Text.Trim());
        //            if (this.txttlspNumber.Text == "0"|| txtnum > number)
        //            {
        //                MessageBox.Show("输入的页数不能大于总页数","系统提示！！");
        //            }
        //            else
        //            {
        //                strat = int.Parse(this.txttlspNumber.Text);
        //                GetSqlPage(strat, int.Parse(treeList_Materials.FocusedNode["f_id"].ToString()));
        //               // this.Number.Text = Convert.ToString(strat);
        //            }
        //        }
        //        else { }
        //    }

        //    //获取总页数 
        //    public void GetAllcount(string level)
        //    {
        //        //MessageBox.Show("积极");
        //        DataTable dt = new DataTable();
        //        string sqlCount = "select count(*) from TS_S_T_B_Materials where f_type=1 and f_del=0 and f_level like '%"+level+"%'";
        //        TS_Win_Class.FilledDataTable(sqlCount, dt);
        //        int count = int.Parse(dt.Rows[0][0].ToString())/5;
        //        if (count == 0)
        //        {
        //            //MessageBox.Show("" + count + "");
        //            this.Number.Text = Convert.ToString(count);
        //        }
        //        else {
        //            count = count + 1;
        //           // MessageBox.Show("" + count + "");
        //            this.Number.Text = Convert.ToString(count);
        //        }       
        //    } 

        //    public void GetSqlPage(int PageNumber,int id)
        //    {
        //        DataTable dt = new DataTable();
        //        string sqlpp = "SELECT TOP 5 f_id,f_name, f_code, f_code_map, f_code_factory, f_code_station, f_code_type, f_cz, f_unit, f_level, f_deep, f_num, f_upnum, f_lownum,  f_indate,f_worktype, case f_worktype when 1 then '线上'  when 0 then '线下' end  as f_worktype1, f_changetype, case f_changetype when 0 then '偶换件' when 1 then '必换件' end  as f_changetype1 from TS_S_T_B_Materials WHERE f_del=0 and f_type=1 and f_level like '%"+id+"%' and f_ID NOT IN(SELECT TOP ("+PageNumber*5+") f_ID FROM TS_S_T_B_Materials where f_del=0 and f_type=1 ORDER BY f_id asc) ORDER BY f_id asc";
        //        TS_Win_Class.FilledDataTable(sqlpp, dt);
        //        Gird_Materials.DataSource = dt;
        //    }


        //    private void tlsdaoNumber_Click(object sender, EventArgs e)
        //    {
        //        SaveFileDialog saveFileDialog = new SaveFileDialog();
        //        saveFileDialog.Filter = "Excel文件(*.xls)|*.xls";
        //        DialogResult dialogResult = saveFileDialog.ShowDialog(this);
        //        if (dialogResult == DialogResult.OK)
        //        {   DataSet ds=new DataSet();
        //            DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions();
        //            gridView1.ExportToXls(saveFileDialog.FileName);
        //            DevExpress.XtraEditors.XtraMessageBox.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        //        }
        //    }

        //    private void tlspdaochuNumber_Click(object sender, EventArgs e)
        //    {
        //        DataTable dt = new DataTable();
        //        TS_Win_Class.FilledDataTable("select f_id,f_name, f_code, f_code_map, f_code_factory, f_code_station, f_code_type, f_cz, f_unit, f_level, f_deep, f_num, f_upnum, f_lownum,  f_indate,f_worktype, case f_worktype when 1 then '线上'  when 0 then '线下' end  as f_worktype1, f_changetype, case f_changetype when 0 then '偶换件' when 1 then '必换件' end  as f_changetype1 from TS_S_T_B_Materials where f_del=0 and f_type=1 ", dt);
        //        Gird_Materials.DataSource = dt;          
        //        SaveFileDialog saveFileDialog = new SaveFileDialog();
        //        saveFileDialog.Filter = "Excel文件(*.xls)|*.xls";
        //        DialogResult dialogResult = saveFileDialog.ShowDialog(this);
        //        if (dialogResult == DialogResult.OK)
        //        {  // gridView2
        //            DataSet ds = new DataSet();
        //            DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions();
        //            Gird_Materials.ExportToXls(saveFileDialog.FileName);
        //            DevExpress.XtraEditors.XtraMessageBox.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        //        }
        //     }

        //    #endregion

    }
 
  }
    


